# MySQL优化的学习
-- 一、查看当前会话sql执行类型的统计信息
show session status like 'com_______';  -- 七个下划线
	# 查看当前会话操作统计结果
show global status like 'com_______';   -- 七个下划线
	# 查看全局（自从上次MySQL服务器启动至今）的操作统计结果
show status like 'innodb_rows_%';
	# 查看针对innodb引擎的统计结果


-- 二、定位低效率执行的sql语句
	-- 2.1.慢查询日志
# 查看慢日志配置信息 
show variables like '%slow_query_log%'; 
# 开启||关闭慢日志查询 
set global slow_query_log = True;  -- 或者等于1
set global slow_query_log = False;  -- 或者等于0
# 查看||改变慢日志记录SQL的最低阈值时间
# 默认的sql执行时间>=10，就算慢日志，会将该操作记录到慢日志中 
show variables like 'long_query_time%'; 
# 修改慢日志记录SQL的最低阈值时间 
set global long_query_time = 4;
select sleep(10.5);

	-- 2.2.定位低效率执行SQL--> show processlist 
use mydb01;
show processlist;
select * from emp2;

	-- 2.3.explain分析执行计划
-- 准备测试数据（运行文件optimize_data.sql）
create database if not exists mydb12_optimize;
use mydb12_optimize;
create table if not exists user(  -- 主表1
	uid int primary key,
	uname varchar(200));
create table if not exists role(   -- 双重主表
	rid int primary key,
	rname varchar(200));
create table if not exists user_role(   -- 中间表1
	uid int,
	rid int);
create table if not exists privilege(     -- 主表2
	pid int primary key,
	pname varchar(200));
create table if not exists role_privilege(    -- 中间表2
	rid int,
	pid int);
-- 创建外键约束
alter table user_role add constraint user_uid_fk 
	foreign key(uid) references user(uid);
alter table user_role add constraint role_rid_fk1 
	foreign key(rid) references role(rid);

alter table role_privilege add constraint role_rid_fk2 
	foreign key(rid) references role(rid);
alter table role_privilege add constraint privilege_pid_fk 
	foreign key(rid) references privilege(pid);
-- 删除外键约束
#alter table user_role drop foreign key user_uid_fk;
#alter table user_role drop foreign key role_rid_fk1;

#alter table role_privilege drop foreign key role_rid_fk2;
#alter table role_privilege drop foreign key privilege_pid_fk;
-- 添加表数据
insert into user values 
	(1, '小乔'),
	(2, '张飞'),
	(3, '貂蝉');
insert into role values 
	(1, '女神'),
	(2, '屌丝'),
	(3, '老板');
insert into privilege values 
	(1, '玩跑车'),
	(2, '挖煤'),
	(3, '敲代码');
	
insert into user_role values 
	(1, 1), (1, 2), (1, 3),
	(2, 1), (2, 2), (2, 3),
	(3, 1), (3, 2), (3, 3);
insert into role_privilege values 
	(1, 1), (1, 2), (1, 3),
	(2, 1), (2, 2), (2, 3),
	(3, 1), (3, 2), (3, 3);

	-- 2.3.1.explain总查询
explain select * from user where uid = 1;
explain select * from user where uname = '张飞';

	-- 2.3.2.explain之id
/* id 字段是 select查询的序列号，是一组数字，
表示的是查询中执行select子句或者是操作表的顺序。id 情况有三种:
	1、id 相同表示加载表的顺序是从上到下。
	2、id 不同id值越大，优先级越高，越先被执行。 
	3、id 有相同，也有不同，同时存在。id相同的可以认为是一组，
		从上往下顺序执行；在所有的组中，id的值越大，优先级越高，
		越先执行。    */ 
explain select * from user u, user_role ur, role r where 
	u.uid = ur.uid and ur.rid = r.rid; -- id相同的查询
explain select * from role where rid = (select rid from 
	user_role where uid = (select uid from user where 
		uname = '张飞') and rid = (select rid from role where 
			rname = '屌丝'));  -- id不同的查询
explain select * from role r, (select * from user_role ur 
	where ur.uid = (select uid from user where uname = '小乔') 
		and ur.rid = (select rid from role where 
			rname = '女神')) t where 
				r.rid = t.rid; -- id有相同，也有不同的查询
	
	-- 2.3.3.explain之select_type
/* 结果值从最好到最坏以此是：
	system > const > eq_ref > ref > range > index > ALL */ 
-- simple：没有子查询和union的查询
explain select * from user;
explain select * from user u, role r where u.uid = r.rid;
-- primary：主查询，包含子查询的最外层查询
-- subquery：在select或where中包含的子查询
explain select * from user_role ur where uid = 
			(select uid from user u where u.uid = ur.uid) and 
				rid = (select rid from role r 
					where r.rid = ur.uid);
-- derived：在from中包含的子查询，标记为衍生表
explain select * from (select * from user_role limit 3, 3) t;
-- union：第二个select出现在union之后被标记为union 
-- union result：从union表获取结果的结果select 
explain select uname from user where uid = 1 union 
	select rname from role where rid = 3;   -- union是上下拼接

	-- 2.3.3.explain之type
-- all：全表扫描，此时id上无索引，效率低
explain select * from user;
-- null：不访问任何表、索引，直接返回结果
explain select now();
explain select rand();  -- 返回0到1的随机数
-- system：系统表，直接从内存读取，5.7以上版本返回all
explain select * from mysql.tables_priv;  -- all
-- const：主键或唯一索引被连接的部分是一个常量（Const）值
explain select uname from user where uid = 2; 
create unique index rname_unique_index on role(rname);
explain select rid from role where rname = '屌丝';  -- all
drop index rname_unique_index on role;

/* eq_ref；对于前表的每一行,后表只有一行被扫描。
	(1)join查询; (2)命中主键(primary key)或者
					非空唯一(unique not null)索引;
	(3)等值连接;  */
create table if not exists user_test(
	id int,
	name varchar(200));
insert into user_test values
	(1, '张三'), (2, '李四'), (3, '王五');
create table if not exists user_eq_ref(
	id int,
	age int);
insert into user_eq_ref values
	(1, 20), (2, 22), (3, 16);
-- 验证：a有主键且b不重复时才eq_ref
explain select * from user_test a, user_eq_ref b where 
	a.id = b.id; -- all 
alter table user_test add primary key(id); 
explain select * from user_test a, user_eq_ref b where 
	a.id = b.id; -- all, eq_ref 
insert into user_eq_ref values(1, 18);
explain select * from user_test a, user_eq_ref b where 
	a.id = b.id; -- all, all 
delete from user_eq_ref where id = 1 and age = 18;
explain select * from user_test a, user_eq_ref b where 
	a.id = b.id; -- all, eq_ref 
alter table user_test drop primary key;

/* ref：非唯一性索引扫描,返回匹配某个单独值的所有行。
	对于前表的每一行(row),后表可能有多于一行的数据被扫描。 
		a有普通索引且b(无普通索引)不重复时ref
		或a有普通索引，b可能重复且有普通索引时ref*/
create index index_id on user_test(id);
explain select * from user_test a, user_eq_ref b where 
	a.id = b.id; -- all, ref 
insert into user_eq_ref values(1, 18);
explain select * from user_test a, user_eq_ref b where 
	a.id = b.id; -- all, all 
create index index_id on user_eq_ref(id);
explain select * from user_test a, user_eq_ref b where 
	a.id = b.id; -- all, ref 
delete from user_eq_ref where id = 1 and age = 18;

drop index index_id on user_eq_ref;
alter table user_test drop index index_id;
drop table if exists user_test;
drop table if exists user_eq_ref;

alter table role add index rname_index(rname);
explain select rid from role where rname = '老板';  -- ref
alter table role drop index rname_index;

-- range：范围查询(>, <, in等运算符出现时)
explain select * from user where uid >= 2;
-- index：把索引的数据全部扫描
explain select uid from user;

	-- 2.3.4.explain之rable
/* 显示这一步所访问数据库中表名称有时不是真实的表名字，
	可能是简称，   */ 
	-- 2.3.5.rows：表示扫描行的数量
	-- 2.3.6.key 
/* 
possible_keys : 显示可能应用在这张表的索引， 一个或多个。 

key ：实际使用的索引， 如果为NULL， 则没有使用索引。

key_len: 表示索引中使用的字节数，该值为索引字段最大可能长度，
	并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。
*/ 
explain select * from user where uid = 1;  
	-- key_len : 4，主键不允许为null，int为4
explain select * from user_role where uid = 1;  
	-- key_len : 5，可以为空，则加上1

	-- extra：其他的额外的执行计划信息，在该列展示 。
/*
using filesort：说明mysql会对数据使用一个外部的索引排序,
	而不是按照表内的索引顺序进行读取,称为“文件排序”,效率低。

using temporary：需要建立临时表(temporary table)来暂存
	中间结果,常见于order by和group by;效率低

using index：SQL所需要返回的所有列数据均在一棵索引树上,
	避免访问表的数据行,效率不错。  */ 
show index from user;
explain select * from user order by uname desc; 
	-- using filesort
explain select count(*) from user group by uname 
	order by uname desc;  -- using temporary, Using filesort
explain select uid, count(*) from user group by uid; 
	-- using index

	-- 2.4.show profile分析sql
#通过 have_profiling 参数，能够看到当前MySQL是否支持profile：
select @@have_profiling;
set profiling = 1 -- profiling开关
# 通过profile，我们能够更清楚地了解SQL执行的过程。
# 首先，我们可以执行一系列的操作
show databases;
use mydb12_optimize;
show tables;
select * from user where uid < 2;
select count(*) from user;
/* 执行完上述命令之后，再执行show profiles 指令，
	来查看SQL语句执行的耗时：  */ 
show profiles; -- MySQL会自动清理，所以每次都往后
/*通过show  profile for  query  query_id 语句
	可以查看到该SQL执行过程中每个线程的状态和消耗的时间： */ 
show profile for query 888;  -- 只能查一次
/* 在获取到最消耗时间的线程状态后，MySQL支持进一步选择
	all、cpu、block io 、context switch、page faults等
	明细类型类查看MySQL在使用什么资源上耗费了过高的时间。
	例如，选择查看CPU的耗费时间： */ 
show profile cpu for query 910;  -- 只能查一次

	-- 2.5.trace分析优化器执行计划
/* 一、打开trace ， 设置格式为 JSON，
并设置trace最大能够使用的内存大小，
避免解析过程中因为默认内存过小而不能够完整展示。  */ 
SET optimizer_trace="enabled=on", end_markers_in_json=on; 
set optimizer_trace_max_mem_size=1000000;  -- 字节为单位
# 二、执行sql语句
select * from user u, user_role ur, role r 
	where u.uid = r.rid;
select * from user where uid < 2;
# 三、检查information_schema.optimizer_trace就可以知道
	# MySQL是如何执行SQL的 ：
select * from information_schema.optimizer_trace\G;
	-- Navicat这里的\G无法识别并执行，可以去cmd终端执行


-- 三、使用索引优化
	-- 数据准备
use mydb12_optimize;
create table if not exists `tb_seller` (
    `sellerid` varchar (100),
    `name` varchar (100),
    `nickname` varchar (50),
    `password` varchar (60),
    `status` varchar (1),
    `address` varchar (100),
    `createtime` datetime,
    primary key(`sellerid`)
); 
insert into `tb_seller` (`sellerid`, `name`, `nickname`, 
	`password`, `status`, `address`, `createtime`) 
	values('alibaba', '阿里巴巴', '阿里小店', 
	'e10adc3949ba59abbe56e057f20f883e', '1', '北京市', 
	'2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, 
	`password`, `status`, `address`, `createtime`) 
	values('baidu', '百度科技有限公司', '百度小店', 
	'e10adc3949ba59abbe56e057f20f883e', '1', '北京市', 
	'2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, 
	`password`, `status`, `address`, `createtime`) 
	values('huawei', '华为科技有限公司', '华为小店', 
	'e10adc3949ba59abbe56e057f20f883e', '0', '北京市', 
	'2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, 
	`password`, `status`, `address`, `createtime`) 
	values('itcast', '传智播客教育科技有限公司', '传智播客',
	'e10adc3949ba59abbe56e057f20f883e', '1', '北京市', 
	'2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, 
	`password`, `status`, `address`, `createtime`) 
	values('itheima', '黑马程序员', null, 
	'e10adc3949ba59abbe56e057f20f883e', '0', '北京市', 
	'2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, 
	`password`, `status`, `address`, `createtime`) 
	values('luoji', '罗技科技有限公司', '罗技小店', 
	'e10adc3949ba59abbe56e057f20f883e', '1', '北京市', 
	'2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, 
	`password`, `status`, `address`, `createtime`) 
	values('oppo', 'OPPO科技有限公司', 'OPPO官方旗舰店', 
	'e10adc3949ba59abbe56e057f20f883e', '0', '北京市', 
	'2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, 
	`password`, `status`, `address`, `createtime`) 
	values('ourpalm', '掌趣科技股份有限公司', '掌趣小店', 
	'e10adc3949ba59abbe56e057f20f883e', '1', '北京市', 
	'2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, 
	`password`, `status`, `address`, `createtime`) 
	values('qiandu', '千度科技', '千度小店', 
	'e10adc3949ba59abbe56e057f20f883e', '2', '北京市', 
	'2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, 
	`password`, `status`, `address`, `createtime`) 
	values('sina', '新浪科技有限公司', '新浪官方旗舰店', 
	'e10adc3949ba59abbe56e057f20f883e', '1', '北京市', 
	'2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, 
	`password`, `status`, `address`, `createtime`) 
	values('xiaomi', '小米科技', '小米官方旗舰店', 
	'e10adc3949ba59abbe56e057f20f883e', '1', '西安市', 
	'2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, 
	`password`, `status`, `address`, `createtime`) 
	values('yijia', '宜家家居', '宜家家居旗舰店', 
	'e10adc3949ba59abbe56e057f20f883e', '1', '北京市', 
	'2088-01-01 12:00:00'); 
	-- 创建组合索引
create index idx_seller_name_sta_addr on 
	tb_seller(name, status, address);
show index from mydb12_optimize.tb_seller;  -- 注意索引顺序
	-- 3.1.避免索引失效应用 --> 全值匹配（效率最高）
	# 字段匹配成功即可，和字段顺序无关
explain select * from tb_seller where name = '小米科技' and 
	status = '1' and address = '西安市';  -- 612
explain select * from tb_seller where address = '西安市' and 
	name = '小米科技' and status = '1';  -- 612
	
	-- 3.2.避免索引失效应用 --> 最左前缀法则（效率较高）
/* 如果索引了多列，要遵守最左前缀法则。
	指的是查询从索引的最左前列开始，并且不跳过索引中的列。 */
explain select * from tb_seller where name = '小米科技'; -- 303
explain select * from tb_seller where 
	name = '小米科技' and status = '1'; -- 309
explain select * from tb_seller where status = '1' 
	and name = '小米科技'; -- 309，与name字段顺序无关
-- 违法最左前缀法则 ， 索引失效：
explain select * from tb_seller where status = '1'; -- nulll
-- 如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：
explain select * from tb_seller where name = '小米科技' 
	and address = '北京市'; -- 303，address索引没有生效
	
	-- 3.3.其他匹配原则
-- 1、范围查询右边的列，不能使用索引。
/* 根据前面的两个字段name ， status 查询是走索引的， 
	但是最后一个条件address 没有用到索引。  */ 
explain select * from tb_seller where name = '小米科技' and 
	status > '1' and address = '西安市';  -- 309
	
-- 2、不要在索引列上进行运算操作， 索引将失效。
explain select * from tb_seller where 
	substring(name,3,2) = '科技';  -- null
	
-- 3、字符串不加单引号，造成索引失效。 
explain select * from tb_seller where name = '小米科技' and 
	status = 1;  -- 303，数字类型索引不生效，能查是因为底层优化
explain select * from tb_seller where name = '小米科技' and 
	status = '1';  -- 309，字符串类型索引生效，符合字段定义类型
	
-- 4、尽量使用覆盖索引，避免select * 
# 需要从原表及磁盘上读取数据
explain select * from tb_seller where name = '小米科技' 
	and address = '西安市';  -- 效率低（Using index condition）
# 从索引树中就可以查询到所有数据
explain select name from tb_seller where name = '小米科技' 
	and address = '西安市';  -- 效率高（Using index）
explain select name, status, address from tb_seller where name = 
	'小米科技' and address = '北京市'; -- 效率高（Using index）
# 如果查询列超出索引列，也会降低性能。
explain select name, status, address, password from tb_seller 
	where name = '小米科技' and address = '北京市'; 
		-- 效率低（Using index condition）
/*
Extra:
using index : 使用覆盖索引的时候就会出现
using where : 在查找使用索引的情况下，
	需要回表去查询所需的数据
using index condition : 查找使用了索引，但是需要回表查询数据
using index; using where : 查找使用了索引，
	但是需要的数据都在索引列中能找到,所以不需要回表查询数据
*/ 

-- 5、用or分割开的条件，那么涉及的索引都不会被用到。
explain select * from tb_seller where name = '黑马程序员' or 
	createtime = '2088-01-01 12:00:00'; -- key_len：null
explain select * from tb_seller where name = '黑马程序员' or 
	address = '西安市';   -- key_len：null
explain select * from tb_seller where name = '黑马程序员' or 
	status = '1';    -- key_len：null
	
-- 6、以%开头的Like模糊查询，索引失效。
explain select * from tb_seller where name like '科技%'; # 303
explain select * from tb_seller where name like '%科技';# null
explain select * from tb_seller where name like '%科技%';# null
# 弥补不足，不用*，使用索引列
explain select name from tb_seller where name like '%科技%';# 612

-- 7、如果MySQL评估使用索引比全表更慢，则不使用索引。
	# 这种情况是由数据本身的特点来决定的
use mydb12_optimize;
drop index index_address on tb_seller;
create index index_address on tb_seller(address);
show index from mydb12_optimize.tb_seller;
explain select * from tb_seller 
	where address = '北京市'; -- 没有使用索引
explain select * from tb_seller 
	where address = '西安市'; -- 使用索引
	
-- 8、is NULL || is NOT NULL  有时有效，有时索引失效。
drop index index_nickname on tb_seller;
create index index_nickname on tb_seller(nickname);
show index from mydb12_optimize.tb_seller;
explain select * from tb_seller where 
	nickname is NULL;  -- 索引有效（数据少）
explain select * from tb_seller where 
	nickname is not NULL; -- 索引无效（数据多）
	
-- 9、in 用到索引，not in 索引失效。
	-- 普通索引
explain select * from tb_seller where 
	nickname in('阿里小店', '百度小店');  -- 索引有效
explain select * from tb_seller where 
	nickname not in('阿里小店', '掌趣小店');  -- 索引无效
	-- 主键索引
explain select * from tb_seller where 
	sellerid in('alibaba', 'ourpalm');  -- 索引有效
explain select * from tb_seller where 
	sellerid not in('alibaba', 'baidu');  -- 索引有效
	
-- 10、单列索引和复合索引，尽量使用符合索引
create index idx_seller_name_sta_addr on 
	tb_seller(name, status, address);
/*	
等价于:
name
name + status
name + status + address
*/ 
/* 如果一张表有多个单列索引,即使where中都使用了这些索引列,
	则只有一个最优索引生效 */ 
drop index idx_seller_name_sta_addr on tb_seller;
show index from tb_seller;
create index index_name on tb_seller(name);
create index index_status on tb_seller(status);
create index index_address on tb_seller(address);
explain select * from tb_seller where name = '小米科技' 
	and status = '1' and address = '西安市';  
		-- 自动选择效率最高的索引使用（index_address）
explain select * from tb_seller where name = '小米科技' 
	and status = '1';  -- 自动选择效率最高的索引使用（name）


-- 四、SQL优化
	-- 创建表
use mydb12_optimize;
drop table if exists tb_user;
create table if not exists `tb_user` (
	`id` int(11) not null auto_increment,
	`username` varchar(45) not null,
	`password` varchar(96) not null,
	`name` varchar(45) not null,
	`birthday` datetime default null,
	`sex` char(1) default null,
	`email` varchar(45) default null,
	`phone` varchar(45) default null,
	`qq` varchar(32) default null,
	`status` varchar(32) not null comment '用户状态',
	`create_time` datetime not null,
	`update_time` datetime default null,
	primary key (`id`),
	unique key `unique_user_username` (`username`)
);
show index from tb_user;
	-- 4.1.大批量插入数据
	/* 4.1.1.检查一个全局系统变量 'local_infile' 的状态， 
		如果得到如下显示 Value=OFF，则说明这是不可用的  */
show global variables like 'local_infile';
-- 4.1.2.修改local_infile值为on，开启local_infile
set global local_infile = 'on'; -- 也可以为True或者1
-- 4.1.3加载数据 
/*
脚本文件介绍 :
    sql1.log  ----> 主键有序
    sql2.log  ----> 主键无序

语法：
load data local infile 'D:\\sql_data\\sql1.log'（文件位置及全名） 
  into table tb_user fields terminated by ','（文件中列之间分隔符） 
	lines terminated by '\n'（换行符）;
*/

	-- 4.2.关闭唯一性效验
/* 在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，
	在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，
	可以提高导入的效率。  ---> 针对唯一索引的情况  */ 
show global variables like '%unique_checks%';   -- 默认打开
SET UNIQUE_CHECKS = 0;    -- 关不掉？？？
truncate table tb_user;
/*  load data local infile 'D:\\sql_data\\sql1.log' into table 
		tb_user fields terminated by ',' 
			lines terminated by '\n';    */ 
SET UNIQUE_CHECKS = 1;

	-- 4.3.优化insert语句
/* 如果需要同时对一张表插入很多行数据时，
	应该尽量使用多个值表的insert语句，
	这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。
	使得效率比分开执行的单个insert语句快。  */ 
create table if not exists tb_test(
	tid int primary key,
	tname varchar(500));
# 原始方式为：
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
# 优化后的方案为：
truncate table tb_test;
insert into tb_test values(1,'Tom'), (2,'Cat'), (3,'Jerry');
# 在事务中进行数据插入
select @@autocommit;  -- 默认为1（自动提交）
set autocommit = 0;  -- 设置事务为手动提交
truncate table tb_test;
begin;   -- 开启事务
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
commit;  -- 提交事务
set autocommit = 'on';  -- 设置事务为自动提交
# 数据有序插入
	# 优化前
truncate table tb_test;
insert into tb_test values(4,'Tim');
insert into tb_test values(1,'Tom');
insert into tb_test values(3,'Jerry');
insert into tb_test values(5,'Rose');
insert into tb_test values(2,'Cat');
	# 优化后
truncate table tb_test;
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
insert into tb_test values(4,'Tim');
insert into tb_test values(5,'Rose');

	-- 4.4.优化order by语句 
# 数据准备
CREATE TABLE IF NOT EXISTS `emp` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`name` varchar(100) NOT NULL,
	`age` int(3) NOT NULL,
	`salary` int(11) DEFAULT NULL,
	PRIMARY KEY (`id`)
);
insert into `emp` (`id`, `name`, `age`, `salary`) 
	values('1', 'Tom', '25', '2300');
insert into `emp` (`id`, `name`, `age`, `salary`) 
	values('2', 'Jerry','30','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) 
	values('3', 'Luci', '25', '2800');
insert into `emp` (`id`, `name`, `age`, `salary`) 
	values('4', 'Jay', '36', '3500');
insert into `emp` (`id`, `name`, `age`, `salary`) 
	values('5', 'Tom2', '21','2200');
insert into `emp` (`id`, `name`, `age`, `salary`) 
	values('6','Jerry2','31','3300');
insert into `emp` (`id`, `name`, `age`, `salary`) 
	values('7', 'Luci2', '26', '2700');
insert into `emp` (`id`, `name`, `age`, `salary`) 
	values('8', 'Jay2', '33', '3500');
insert into `emp` (`id`, `name`, `age`, `salary`) 
	values('9', 'Tom3', '23', '2400');
insert into `emp` (`id`, `name`, `age`, `salary`) 
	values('10','Jerry3','32','3100');
insert into `emp` (`id`, `name`, `age`, `salary`) 
	values('11', 'Luci3', '26', '2900');
insert into `emp` (`id`, `name`, `age`, `salary`) 
	values('12', 'Jay3', '37', '4500');
# 创建组合索引
create index idx_emp_age_salary on emp(age, salary);
# 排序查询
explain select * from emp order by age desc;  
	-- Using filesort，效率低
explain select * from emp order by age, salary desc;  
	-- Using filesort，效率低
explain select id, salary from emp order by age desc;  
	-- Backward index scan; Using index，效率较高（id是主键）
explain select id, age, salary, name from emp order by age;  
	-- Using filesort，效率高
explain select id, age, salary from emp order by age;  
	-- Using index，效率高
explain select id, age from emp order by age asc, salary desc;
	-- Using index; Using filesort，效率受影响
explain select id, age from emp order by age desc, salary desc;
	-- Backward index scan; Using index
explain select id, age from emp order by salary, age;
	-- Using index; Using filesort，效率受影响
/* order by 后面的多个字段要求尽量排序方式相同且字段顺序尽量和
	组合索引的字段定义顺序一致  */
# filesort的优化
/* MySQL 通过比较系统变量 max_length_for_sort_data 的大小和
	Query语句取出的字段总大小，来判定是否那种排序算法，
	如果max_length_for_sort_data 更大，
	那么使用第二种优化之后的算法；否则使用第一种。

可以适当提高 sort_buffer_size  和 max_length_for_sort_data  
	系统变量，来增大排序区的大小，提高排序的效率。  */ 
select @@sort_buffer_size;   -- 字节
select @@max_length_for_sort_data;  -- kb（4K对齐）
show variables like '%sort_buffer_size%';
show variables like '%max_length_for_sort_data%';

	-- 4.5.优化group by
/* 在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。
如果查询包含 group by 但是用户想要避免排序结果的消耗， 
	则可以执行order by null 禁止排序。如下：  */ 
drop index idx_emp_age_salary on emp; 
explain select age,count(*) from emp group by age;
explain select age,count(*) from emp group by age order by null;
create index idx_emp_age_salary on emp(age, salary);

	-- 4.6.优化子查询
# 结论：多表查询效率大于子查询效率，子查询可以被join查询代替
explain select * from user where uid in 
	(select uid from user_role );   -- type：index, all
explain select * from user u , user_role ur where 
	u.uid = ur.uid;  -- type：all, ref
# 说明：system > const > eq_ref > ref > range > index > ALL

	-- 4.7.优化limit查询
/* 一、在索引上完成排序分页操作，
	最后根据主键关联回原表查询所需要的其他列内容。  */ 
select count(1) from emp;
select * from emp limit 0, 5;  -- 快（从开始查）
explain select * from emp limit 6, 5# 慢（从中间查，扫描之前数据）
explain select * from emp a join (select * from emp limit 6, 5) b 
	on a.id = b.id;  -- 用了索引，效率较快
show index from emp;
# 对应于主键自增的表，可以把 Limit 查询转换成某个位置的查询 。
explain select * from emp where id > 6 limit 5;  -- 快
